<?php
class ASmsProduct extends CActiveRecord
{
    public function getAllserviceNumber()
    {
        $sql = "SELECT DISTINCT (serviceNumber) FROM c_sms_log ORDER BY serviceNumber";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;
    }
    
    public function GetProduct($search, $currentPage, $numberRecordPerPage)
    {
        $search["fromDate"] .= " 23:59:59";
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $condition = "";        
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        $condition .= !empty($search["type"]) ? " AND type=" . mysql_escape_string($search["type"]) : "";        
        $condition .= " AND status=1";        
        
        if(!empty($search["keyword"])){
            if(empty($search["type"]) || $search["type"]==1){
                
                $sql = "SELECT id FROM c_game WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();        
                $condition .= !empty($rows) ? " AND type='" . mysql_escape_string($search["type"]) . "' AND dataId='" . mysql_escape_string($rows["id"]) . "'" : " AND 1=2";
                
            } else if(empty($search["type"]) || $search["type"]==2){
                
                $sql = "SELECT id FROM c_video WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();        
                $condition .= !empty($rows) ? " AND type='" . mysql_escape_string($search["type"]) . "' AND dataId='" . mysql_escape_string($rows["id"]) . "'" : " AND 1=2";
                
            } else if(empty($search["type"]) || $search["type"]==3){
                
                $sql = "SELECT id FROM c_album WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();        
                $condition .= !empty($rows) ? " AND type='" . mysql_escape_string($search["type"]) . "' AND dataId='" . mysql_escape_string($rows["id"]) . "'" : " AND 1=2";
                
            } else if(empty($search["type"]) || $search["type"]==4){
                
                $sql = "SELECT id FROM c_ring_back WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();        
                $condition .= !empty($rows) ? " AND type='" . mysql_escape_string($search["type"]) . "' AND dataId='" . mysql_escape_string($rows["id"]) . "'" : " AND 1=2";
                
            } else if(empty($search["type"]) || $search["type"]==6){
                
                $sql = "SELECT id FROM c_sms_kute WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();        
                $condition .= !empty($rows) ? " AND type='" . mysql_escape_string($search["type"]) . "' AND dataId='" . mysql_escape_string($rows["id"]) . "'" : " AND 1=2";
                
            }                                                
        }
          
        /* Tiêu chí sắp xếp mặc định */      
        $sql = "SELECT dataId, type, sum(price) as total_price FROM c_sms_log WHERE 1 " . $condition . " GROUP BY dataId, type ORDER BY total_price DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        
        if($search["order"]=="1") {
            /* Sắp xếp theo doanh thu từ thấp đến cao */
            $sql = "SELECT dataId, type, sum(price) as total_price FROM c_sms_log WHERE 1 " . $condition . " GROUP BY dataId, type ORDER BY total_price ASC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="2") {
            /* Sắp xếp theo doanh thu từ cao đến thấp */
            $sql = "SELECT dataId, type, sum(price) as total_price FROM c_sms_log WHERE 1 " . $condition . " GROUP BY dataId, type ORDER BY total_price DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="3") {
            /* Sắp xếp theo sản lượng từ thấp đến cao */
            $sql = "SELECT dataId, type, count(id) as total_sms FROM c_sms_log WHERE 1 " . $condition . " GROUP BY dataId, type ORDER BY total_sms ASC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="4") {
            /* Sắp xếp theo sản lượng từ cao đến thấp */
            $sql = "SELECT dataId, type, count(id) as total_sms FROM c_sms_log WHERE 1 " . $condition . " GROUP BY dataId, type ORDER BY total_sms DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        }  
        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();    
        return $rows;
    }
    
    public function GetTotalProduct($search)
    {
        $search["fromDate"] .= " 23:59:59";
        $condition = "";        
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        $condition .= !empty($search["type"]) ? " AND type=" . mysql_escape_string($search["type"]) : "";        
        $condition .= " AND status=1";        
        
        if(!empty($search["keyword"])){
            if(empty($search["type"]) || $search["type"]==1){
                
                $sql = "SELECT id FROM c_game WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();        
                $condition .= !empty($rows) ? " AND type='" . mysql_escape_string($search["type"]) . "' AND dataId='" . mysql_escape_string($rows["id"]) . "'" : " AND 1=2";
                
            } else if(empty($search["type"]) || $search["type"]==2){
                
                $sql = "SELECT id FROM c_video WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();        
                $condition .= !empty($rows) ? " AND type='" . mysql_escape_string($search["type"]) . "' AND dataId='" . mysql_escape_string($rows["id"]) . "'" : " AND 1=2";
                
            } else if(empty($search["type"]) || $search["type"]==3){
                
                $sql = "SELECT id FROM c_album WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();        
                $condition .= !empty($rows) ? " AND type='" . mysql_escape_string($search["type"]) . "' AND dataId='" . mysql_escape_string($rows["id"]) . "'" : " AND 1=2";
                
            } else if(empty($search["type"]) || $search["type"]==4){
                
                $sql = "SELECT id FROM c_ring_back WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();        
                $condition .= !empty($rows) ? " AND type='" . mysql_escape_string($search["type"]) . "' AND dataId='" . mysql_escape_string($rows["id"]) . "'" : " AND 1=2";
                
            } else if(empty($search["type"]) || $search["type"]==6){
                
                $sql = "SELECT id FROM c_sms_kute WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();        
                $condition .= !empty($rows) ? " AND type='" . mysql_escape_string($search["type"]) . "' AND dataId='" . mysql_escape_string($rows["id"]) . "'" : " AND 1=2";
                
            }                                                
        }
                
        $sql = "SELECT count(total) FROM (SELECT count(id) as total FROM c_sms_log WHERE 1 " . $condition . " GROUP BY dataId, type) temp";          
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();            
        return $rows["count(total)"];
    }
    
    public function getReport($search, $rate, $rate_sms, $order)
    {        
        $search["fromDate"] .= " 23:59:59";
        $condition = "";        
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        
        $sms = array();
        
        if(!empty($search["list_game_id"])){
            $condition_game = " AND type=1 AND dataId IN (" . mysql_escape_string($search["list_game_id"]) . ")";
            
            $sql = "SELECT count(id), price, dataId, serviceNumber, percent_wapmaster, percent_partner, month, year FROM c_sms_log WHERE status=1 " . $condition_game . $condition . " GROUP BY dataId, price, percent_wapmaster, percent_partner, serviceNumber, month, year";        
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            
            /* lấy tên game */
            $sql = "SELECT id, title FROM c_game WHERE id IN (" . $search["list_game_id"] . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsGame = $command->queryAll();
            $replaceGame = array();
            
            foreach($rowsGame as $row){                
                $replaceGame[$row["id"]]["title"] = $row["title"];
            }
            
            $i = 0;
            while(isset($rows[$i])){                
                $rows[$i]["title"] = isset($replaceGame[$rows[$i]["dataId"]]["title"]) ? $replaceGame[$rows[$i]["dataId"]]["title"] : "";
                $i++;
            }
            
            foreach($rows as $row){
                $id = $row["dataId"];                        
                $type = 1;
                
                $index = $id . "_" . $type;
                            
                if(!isset($sms[$index]["total_sms"])) $sms[$index]["total_sms"] = 0;
                if(!isset($sms[$index]["5000"])) $sms[$index]["5000"] = 0;
                if(!isset($sms[$index]["10000"])) $sms[$index]["10000"] = 0;
                if(!isset($sms[$index]["15000"])) $sms[$index]["15000"] = 0;
                if(!isset($sms[$index]["telco"])) $sms[$index]["telco"] = 0;
                if(!isset($sms[$index]["serviceNumber"])) $sms[$index]["serviceNumber"] = 0;
                if(!isset($sms[$index]["wapmaster"])) $sms[$index]["wapmaster"] = 0;
                if(!isset($sms[$index]["mtop"])) $sms[$index]["mtop"] = 0;
                if(!isset($sms[$index]["partner"])) $sms[$index]["partner"] = 0;
                
                $sms[$index]["title"] = $row["title"];
                $sms[$index]["type"] = $type;
                $sms[$index]["total_sms"] += $row["count(id)"];
                $sms[$index]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
                $sms[$index]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
                $sms[$index]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
                
                $total_money = $row["price"] * $row["count(id)"];
                
                $index_rate = $row["month"] . "_" . $row["year"];
                $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
                $rateSms = $rate_sms[$index_rate][$sc] / 100;
                
                $sms[$index]["telco"] += $total_money * $rateSms;  
                $sms[$index]["serviceNumber"] += $total_money * $rateSms * 0.85;  
                      
                $sms[$index]["wapmaster"] += $total_money * $rateSms * 0.85 * ($row["percent_wapmaster"] / 100);
                $sms[$index]["partner"] += $total_money * $rateSms * 0.85 * ($row["percent_partner"] / 100);
                $sms[$index]["mtop"] += $total_money * $rateSms * 0.85 * ((100 - $row["percent_wapmaster"] - $row["percent_partner"]) / 100);
            }
        }
        
        if(!empty($search["list_video_id"])){
            $condition_video = " AND type=2 AND dataId IN (" . mysql_escape_string($search["list_video_id"]) . ")";
            
            $sql = "SELECT count(id), price, dataId, month, year, serviceNumber, type FROM c_sms_log WHERE status=1 " . $condition_video . $condition . " GROUP BY dataId, price, month, year, serviceNumber";        
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            
            /* lấy tên video */
            $sql = "SELECT id, title FROM c_video WHERE id IN (" . $search["list_video_id"] . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsVideo = $command->queryAll();
            $replaceVideo = array();
            
            foreach($rowsVideo as $row){                
                $replaceVideo[$row["id"]]["title"] = $row["title"];
            }
            
            $i = 0;
            while(isset($rows[$i])){                
                $rows[$i]["title"] = isset($replaceVideo[$rows[$i]["dataId"]]["title"]) ? $replaceVideo[$rows[$i]["dataId"]]["title"] : "";
                $i++;
            }
            
            foreach($rows as $row){
                $id = $row["dataId"];                        
                $type = 2;
                
                $index = $id . "_" . $type;
                $index_rate = $row["month"] . "_" . $row["year"];
                $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
                $type = $row["type"];
                            
                if(!isset($sms[$index]["total_sms"])) $sms[$index]["total_sms"] = 0;
                if(!isset($sms[$index]["5000"])) $sms[$index]["5000"] = 0;
                if(!isset($sms[$index]["10000"])) $sms[$index]["10000"] = 0;
                if(!isset($sms[$index]["15000"])) $sms[$index]["15000"] = 0;
                if(!isset($sms[$index]["telco"])) $sms[$index]["telco"] = 0;
                if(!isset($sms[$index]["serviceNumber"])) $sms[$index]["serviceNumber"] = 0;
                if(!isset($sms[$index]["wapmaster"])) $sms[$index]["wapmaster"] = 0;
                if(!isset($sms[$index]["mtop"])) $sms[$index]["mtop"] = 0;
                if(!isset($sms[$index]["partner"])) $sms[$index]["partner"] = 0;
                
                $sms[$index]["title"] = $row["title"];
                $sms[$index]["type"] = $type;
                $sms[$index]["total_sms"] += $row["count(id)"];
                $sms[$index]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
                $sms[$index]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
                $sms[$index]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
                
                $total_money = $row["price"] * $row["count(id)"];
            
                $rateSms = $rate_sms[$index_rate][$sc] / 100;
                $sms[$index]["telco"] += $total_money * $rateSms;  
                $sms[$index]["serviceNumber"] += $total_money * $rateSms * 0.85;  
                            
                $sms[$index]["mtop"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_mtop"] / 100);
                $sms[$index]["wapmaster"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_wapmaster"] / 100);
            }
        } 
        
        if(!empty($search["list_album_id"])){
            $condition_album = " AND type=3 AND dataId IN (" . mysql_escape_string($search["list_album_id"]) . ")";
            
            $sql = "SELECT count(id), price, dataId, month, year, type, serviceNumber FROM c_sms_log WHERE status=1 " . $condition_album . $condition . " GROUP BY dataId, price, month, year";        
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            
            /* lấy tên video */
            $sql = "SELECT id, title FROM c_album WHERE id IN (" . $search["list_album_id"] . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsAlbum = $command->queryAll();
            $replaceAlbum = array();
            
            foreach($rowsAlbum as $row){                
                $replaceAlbum[$row["id"]]["title"] = $row["title"];
            }
            
            $i = 0;
            while(isset($rows[$i])){                
                $rows[$i]["title"] = isset($replaceAlbum[$rows[$i]["dataId"]]["title"]) ? $replaceAlbum[$rows[$i]["dataId"]]["title"] : "";
                $i++;
            }
            
            foreach($rows as $row){
                $id = $row["dataId"];                        
                $type = 3;
                
                $index = $id . "_" . $type;
                $index_rate = $row["month"] . "_" . $row["year"];
                $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
                $type = $row["type"];
                            
                if(!isset($sms[$index]["total_sms"])) $sms[$index]["total_sms"] = 0;
                if(!isset($sms[$index]["5000"])) $sms[$index]["5000"] = 0;
                if(!isset($sms[$index]["10000"])) $sms[$index]["10000"] = 0;
                if(!isset($sms[$index]["15000"])) $sms[$index]["15000"] = 0;
                if(!isset($sms[$index]["telco"])) $sms[$index]["telco"] = 0;
                if(!isset($sms[$index]["serviceNumber"])) $sms[$index]["serviceNumber"] = 0;
                if(!isset($sms[$index]["wapmaster"])) $sms[$index]["wapmaster"] = 0;
                if(!isset($sms[$index]["mtop"])) $sms[$index]["mtop"] = 0;
                if(!isset($sms[$index]["partner"])) $sms[$index]["partner"] = 0;
                
                $sms[$index]["title"] = $row["title"];
                $sms[$index]["type"] = $type;
                $sms[$index]["total_sms"] += $row["count(id)"];
                $sms[$index]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
                $sms[$index]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
                $sms[$index]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
                
                $total_money = $row["price"] * $row["count(id)"];
            
                $rateSms = $rate_sms[$index_rate][$sc] / 100;
                $sms[$index]["telco"] += $total_money * $rateSms;  
                $sms[$index]["serviceNumber"] += $total_money * $rateSms * 0.85;  
                            
                $sms[$index]["mtop"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_mtop"] / 100);
                $sms[$index]["wapmaster"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_wapmaster"] / 100);
            }
        }
        
        if(!empty($search["list_style_id"])){
            $condition_album = " AND type=4 AND dataId IN (" . mysql_escape_string($search["list_style_id"]) . ")";
            
            $sql = "SELECT count(id), price, dataId, month, year, type, serviceNumber FROM c_sms_log WHERE status=1 " . $condition_album . $condition . " GROUP BY dataId, price, month, year";        
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            
            /* lấy tên video */
            $sql = "SELECT id, title FROM c_ring_back WHERE id IN (" . $search["list_style_id"] . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsStyle = $command->queryAll();
            $replaceStyle = array();
            
            foreach($rowsStyle as $row){                
                $replaceStyle[$row["id"]]["title"] = $row["title"];
            }
            
            $i = 0;
            while(isset($rows[$i])){                
                $rows[$i]["title"] = isset($replaceStyle[$rows[$i]["dataId"]]["title"]) ? $replaceStyle[$rows[$i]["dataId"]]["title"] : "";
                $i++;
            }
            
            foreach($rows as $row){
                $id = $row["dataId"];                        
                $type = 4;
                
                $index = $id . "_" . $type;
                $index_rate = $row["month"] . "_" . $row["year"];
                $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
                $type = $row["type"];
                            
                if(!isset($sms[$index]["total_sms"])) $sms[$index]["total_sms"] = 0;
                if(!isset($sms[$index]["5000"])) $sms[$index]["5000"] = 0;
                if(!isset($sms[$index]["10000"])) $sms[$index]["10000"] = 0;
                if(!isset($sms[$index]["15000"])) $sms[$index]["15000"] = 0;
                if(!isset($sms[$index]["telco"])) $sms[$index]["telco"] = 0;
                if(!isset($sms[$index]["serviceNumber"])) $sms[$index]["serviceNumber"] = 0;
                if(!isset($sms[$index]["wapmaster"])) $sms[$index]["wapmaster"] = 0;
                if(!isset($sms[$index]["mtop"])) $sms[$index]["mtop"] = 0;
                if(!isset($sms[$index]["partner"])) $sms[$index]["partner"] = 0;
                
                $sms[$index]["title"] = $row["title"];
                $sms[$index]["type"] = $type;
                $sms[$index]["total_sms"] += $row["count(id)"];
                $sms[$index]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
                $sms[$index]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
                $sms[$index]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
                
                $total_money = $row["price"] * $row["count(id)"];
            
                $rateSms = $rate_sms[$index_rate][$sc] / 100;
                $sms[$index]["telco"] += $total_money * $rateSms;  
                $sms[$index]["serviceNumber"] += $total_money * $rateSms * 0.85;  
                            
                $sms[$index]["mtop"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_mtop"] / 100);
                $sms[$index]["wapmaster"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_wapmaster"] / 100);
            }
        }
        
        if(!empty($search["list_sms_id"])){
            $condition_album = " AND type=6 AND dataId IN (" . mysql_escape_string($search["list_sms_id"]) . ")";
            
            $sql = "SELECT count(id), price, dataId, month, year, type, serviceNumber FROM c_sms_log WHERE status=1 " . $condition_album . $condition . " GROUP BY dataId, price, month, year";        
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            
            /* lấy tên video */
            $sql = "SELECT id, title FROM c_sms_kute WHERE id IN (" . $search["list_sms_id"] . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsKute = $command->queryAll();
            $replaceKute = array();
            
            foreach($rowsKute as $row){                
                $replaceKute[$row["id"]]["title"] = $row["title"];
            }
            
            $i = 0;
            while(isset($rows[$i])){                
                $rows[$i]["title"] = isset($replaceKute[$rows[$i]["dataId"]]["title"]) ? $replaceKute[$rows[$i]["dataId"]]["title"] : "";
                $i++;
            }
            
            foreach($rows as $row){
                $id = $row["dataId"];                        
                $type = 6;
                
                $index = $id . "_" . $type;
                $index_rate = $row["month"] . "_" . $row["year"];
                $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
                $type = $row["type"];
                            
                if(!isset($sms[$index]["total_sms"])) $sms[$index]["total_sms"] = 0;
                if(!isset($sms[$index]["5000"])) $sms[$index]["5000"] = 0;
                if(!isset($sms[$index]["10000"])) $sms[$index]["10000"] = 0;
                if(!isset($sms[$index]["15000"])) $sms[$index]["15000"] = 0;
                if(!isset($sms[$index]["telco"])) $sms[$index]["telco"] = 0;
                if(!isset($sms[$index]["serviceNumber"])) $sms[$index]["serviceNumber"] = 0;
                if(!isset($sms[$index]["wapmaster"])) $sms[$index]["wapmaster"] = 0;
                if(!isset($sms[$index]["mtop"])) $sms[$index]["mtop"] = 0;
                if(!isset($sms[$index]["partner"])) $sms[$index]["partner"] = 0;
                
                $sms[$index]["title"] = $row["title"];
                $sms[$index]["type"] = $type;
                $sms[$index]["total_sms"] += $row["count(id)"];
                $sms[$index]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
                $sms[$index]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
                $sms[$index]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
                
                $total_money = $row["price"] * $row["count(id)"];
            
                $rateSms = $rate_sms[$index_rate][$sc] / 100;
                $sms[$index]["telco"] += $total_money * $rateSms;  
                $sms[$index]["serviceNumber"] += $total_money * $rateSms * 0.85;  
                            
                $sms[$index]["mtop"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_mtop"] / 100);
                $sms[$index]["wapmaster"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_wapmaster"] / 100);
            }
        }
        
        $condition_soi = " AND type=5";
        $sql = "SELECT count(id), price, dataId, month, year, type, serviceNumber FROM c_sms_log WHERE status=1 " . $condition_soi . $condition . " GROUP BY dataId, price, month, year";        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
              
        foreach($rows as $row){
            $id = $row["dataId"];                        
            $type = 5;
            
            $index = $id . "_" . $type;
            $index_rate = $row["month"] . "_" . $row["year"];
            $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
            $type = $row["type"];
                        
            if(!isset($sms[$index]["total_sms"])) $sms[$index]["total_sms"] = 0;
            if(!isset($sms[$index]["5000"])) $sms[$index]["5000"] = 0;
            if(!isset($sms[$index]["10000"])) $sms[$index]["10000"] = 0;
            if(!isset($sms[$index]["15000"])) $sms[$index]["15000"] = 0;
            if(!isset($sms[$index]["telco"])) $sms[$index]["telco"] = 0;
            if(!isset($sms[$index]["serviceNumber"])) $sms[$index]["serviceNumber"] = 0;
            if(!isset($sms[$index]["wapmaster"])) $sms[$index]["wapmaster"] = 0;
            if(!isset($sms[$index]["mtop"])) $sms[$index]["mtop"] = 0;
            if(!isset($sms[$index]["partner"])) $sms[$index]["partner"] = 0;
            
            $sms[$index]["title"] = "Không xác định";
            $sms[$index]["type"] = $type;
            $sms[$index]["total_sms"] += $row["count(id)"];
            $sms[$index]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
            $sms[$index]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
            $sms[$index]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
            
            $total_money = $row["price"] * $row["count(id)"];
        
            $rateSms = $rate_sms[$index_rate][$sc] / 100;
            $sms[$index]["telco"] += $total_money * $rateSms;  
            $sms[$index]["serviceNumber"] += $total_money * $rateSms * 0.85;  
                        
            $sms[$index]["mtop"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_mtop"] / 100);
            $sms[$index]["wapmaster"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_wapmaster"] / 100);
        }
        
        $condition_test = " AND type=7";
        $sql = "SELECT count(id), price, dataId, month, year, type, serviceNumber FROM c_sms_log WHERE status=1 " . $condition_test . $condition . " GROUP BY dataId, price, month, year";        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
              
        foreach($rows as $row){
            $id = $row["dataId"];                        
            $type = 7;
            
            $index = $id . "_" . $type;
            $index_rate = $row["month"] . "_" . $row["year"];
            $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
            $type = $row["type"];
                        
            if(!isset($sms[$index]["total_sms"])) $sms[$index]["total_sms"] = 0;
            if(!isset($sms[$index]["5000"])) $sms[$index]["5000"] = 0;
            if(!isset($sms[$index]["10000"])) $sms[$index]["10000"] = 0;
            if(!isset($sms[$index]["15000"])) $sms[$index]["15000"] = 0;
            if(!isset($sms[$index]["telco"])) $sms[$index]["telco"] = 0;
            if(!isset($sms[$index]["serviceNumber"])) $sms[$index]["serviceNumber"] = 0;
            if(!isset($sms[$index]["wapmaster"])) $sms[$index]["wapmaster"] = 0;
            if(!isset($sms[$index]["mtop"])) $sms[$index]["mtop"] = 0;
            if(!isset($sms[$index]["partner"])) $sms[$index]["partner"] = 0;
            
            $sms[$index]["title"] = "Không xác định";
            $sms[$index]["type"] = $type;
            $sms[$index]["total_sms"] += $row["count(id)"];
            $sms[$index]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
            $sms[$index]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
            $sms[$index]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
            
            $total_money = $row["price"] * $row["count(id)"];
        
            $rateSms = $rate_sms[$index_rate][$sc] / 100;
            $sms[$index]["telco"] += $total_money * $rateSms;  
            $sms[$index]["serviceNumber"] += $total_money * $rateSms * 0.85;  
                        
            $sms[$index]["mtop"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_mtop"] / 100);
            $sms[$index]["wapmaster"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_wapmaster"] / 100);
        }
        
        $condition_mod = " AND type=8";
        $sql = "SELECT count(id), price, dataId, month, year, type, serviceNumber FROM c_sms_log WHERE status=1 " . $condition_mod . $condition . " GROUP BY dataId, price, month, year";        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
              
        foreach($rows as $row){
            $id = $row["dataId"];                        
            $type = 8;
            
            $index = $id . "_" . $type;
            $index_rate = $row["month"] . "_" . $row["year"];
            $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
            $type = $row["type"];
                        
            if(!isset($sms[$index]["total_sms"])) $sms[$index]["total_sms"] = 0;
            if(!isset($sms[$index]["5000"])) $sms[$index]["5000"] = 0;
            if(!isset($sms[$index]["10000"])) $sms[$index]["10000"] = 0;
            if(!isset($sms[$index]["15000"])) $sms[$index]["15000"] = 0;
            if(!isset($sms[$index]["telco"])) $sms[$index]["telco"] = 0;
            if(!isset($sms[$index]["serviceNumber"])) $sms[$index]["serviceNumber"] = 0;
            if(!isset($sms[$index]["wapmaster"])) $sms[$index]["wapmaster"] = 0;
            if(!isset($sms[$index]["mtop"])) $sms[$index]["mtop"] = 0;
            if(!isset($sms[$index]["partner"])) $sms[$index]["partner"] = 0;
            
            $sms[$index]["title"] = "Không xác định";
            $sms[$index]["type"] = $type;
            $sms[$index]["total_sms"] += $row["count(id)"];
            $sms[$index]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
            $sms[$index]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
            $sms[$index]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
            
            $total_money = $row["price"] * $row["count(id)"];
        
            $rateSms = $rate_sms[$index_rate][$sc] / 100;
            $sms[$index]["telco"] += $total_money * $rateSms;  
            $sms[$index]["serviceNumber"] += $total_money * $rateSms * 0.85;  
                        
            $sms[$index]["mtop"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_mtop"] / 100);
            $sms[$index]["wapmaster"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_wapmaster"] / 100);
        }  
        
        $response = array();
        foreach($order as $index=>$i){
            if(isset($sms[$index])){
                $response[$index] = $sms[$index];
            }   
        }
        
        return $response;
    }
    
    public function getReportByProduct($search, $rate, $rate_sms, $currentPage, $numberRecordPerPage)
    {
        $search["fromDate"] .= " 23:59:59";
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $condition = "";
        $condition .= " AND dataId=" . $search["dataId"];
        $condition .= " AND type=" . $search["type"];
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";                
        $condition .= " AND status=1";                
        
        $sql = "SELECT * FROM c_sms_log WHERE 1 " . $condition . " ORDER BY create_date DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
                         
        $i = 0;
        while(isset($rows[$i])){
            $type = $rows[$i]["type"];            
            $index_rate = $rows[$i]["month"] . "_" . $rows[$i]["year"];
            $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $rows[$i]["serviceNumber"]);
            $rateSms = $rate_sms[$index_rate][$sc] / 100;
             
            $rows[$i]["telco"] = $rows[$i]["price"] * $rateSms;  
            $rows[$i]["sc"] = $rows[$i]["price"] * $rateSms * 0.85;  
            if($rows[$i]["type"]=="1"){
                $rows[$i]["wapmaster"] = $rows[$i]["sc"] * ($rows[$i]["percent_wapmaster"] / 100);
                $rows[$i]["partner"] = $rows[$i]["sc"] * ($rows[$i]["percent_partner"] / 100);
                $rows[$i]["mtop"] = $rows[$i]["sc"] * ((100 - $rows[$i]["percent_wapmaster"] - $rows[$i]["percent_partner"]) / 100);
            } else {                
                $rows[$i]["wapmaster"] = $rows[$i]["sc"] * ($rate[$index_rate][$type]["percent_wapmaster"] / 100);
                $rows[$i]["partner"] = 0;
                $rows[$i]["mtop"] = $rows[$i]["sc"] * ($rate[$index_rate][$type]["percent_mtop"] / 100);                 
            }
            
            $i++;
        }
        
        return $rows;
    }
    
    public function getTotalReportByProduct($search)
    {
        $search["fromDate"] .= " 23:59:59";
        
        $condition = "";
        $condition .= " AND dataId=" . $search["dataId"];
        $condition .= " AND type=" . $search["type"];
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";                
        $condition .= " AND status=1";
        
        $sql = "SELECT count(id) FROM c_sms_log WHERE 1 " . $condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();        
        return $rows["count(id)"];
    }
}  
?>